﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;

using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class LocationComponent : BaseSQLModel
    {
         #region private properties
        private const string SQL_QUERY = "SELECT [location_id],[wh_master_id],[pick_area_id],[location],[description] " +
                            ",[loc_type],[location_status],[length],[width],[height],[capacity_qty],[lpn_controlled] " +
                            " ,[mix_owners],[mix_items],[mix_lot],[mix_expiry_dates],[putaway_sequence],[pick_sequence] " +
                            ",[override_receive_date],[is_active],[create_date],[create_by] " +
                            " FROM [dbo].[t_eics_location] order by [location]";

        private const string SQL_PROC_QUERY = "";

        private const string SQL_INSERT = "";

        private const string SQL_PROC_INSERT = "usp_InsertLocationRecord " +
                                        "@in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], @in_vchUserID = [@UserId] " +
                                        ",@in_vchWhMasterID  = [@WhId] " +
                                        ",@in_vchPickAreaID = [@PickAreaId] " +
                                        ",@in_vchLocation		 = [@LocationName] " +
                                        ",@in_vchDescription = [@Description] "+
                                        ",@in_vchLoc_type	 = [@LocationType] " +
                                        ",@in_vchLocationStatus = [@Status] " +
                                        ",@in_intLength = [@Length] " +
                                        ",@in_intWidth = [@Width] " +
                                        ",@in_intHeight = [@Height] " +
                                        ",@in_intCapacityQty	 = [@Capacity] " +
                                        ",@in_vchLpnControlled= [@LpnControl] " +
                                        ",@in_vchMixOwners = [@MixOwners] " +
                                        ",@in_vchMixItems	= [MixItems] " +
                                        ",@in_vchMixLot	 = [@MixLot] " +
                                        ",@in_vchMixExpiryDates	 = [@MixExpireDate] " +
                                        ",@in_intPutawaySequence = [@PutAway] " +
                                        ",@in_intPickSequence		= [@PickSequence] " +
                                        ",@in_vchOverrideReceiveDate = [@OverrideReceiveDate] " +
                                        ",@in_vchIsActive	 = [@IsActive]";

        private const string SQL_UPDATE = "";

        private const string SQL_PROC_UPDATE = "usp_UpdateLocationRecord " +
                        "@in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], @in_vchUserID = [@UserId] " +
                                        ",@in_vchWhMasterID  = [@WhId] " +
                                        ",@in_vchLocationID = [@LocationId] " +
                                        ",@in_vchPickAreaID = [@PickAreaId] " +
                                        ",@in_vchLocation		 = [@LocationName] " +
                                        ",@in_vchDescription = [@Description] " +
                                        ",@in_vchLoc_type	 = [@LocationType] " +
                                        ",@in_vchLocationStatus = [@Status] " +
                                        ",@in_intLength = [@Length] " +
                                        ",@in_intWidth = [@Width] " +
                                        ",@in_intHeight = [@Height] " +
                                        ",@in_intCapacityQty	 = [@Capacity] " +
                                        ",@in_vchLpnControlled= [@LpnControl] " +
                                        ",@in_vchMixOwners = [@MixOwners] " +
                                        ",@in_vchMixItems	= [MixItems] " +
                                        ",@in_vchMixLot	 = [@MixLot] " +
                                        ",@in_vchMixExpiryDates	 = [@MixExpireDate] " +
                                        ",@in_intPutawaySequence = [@PutAway] " +
                                        ",@in_intPickSequence		= [@PickSequence] " +
                                        ",@in_vchOverrideReceiveDate = [@OverrideReceiveDate] " +
                                        ",@in_vchIsActive	 = [@IsActive]";

        private const string SQL_DELETE = "";

        private const string SQL_PROC_DELETE = "usp_DeleteLocationRecord @in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], "+
            "@in_vchUserID = [@UserId], @in_vchLocationID = [@LocationId], @in_vchIsActive = [@IsActive]";

        private const string SQL_GET_WH_MASTER_ID = "SELECT wh_master_id FROM t_eics_wh";
        private const string dataTableName = "Location";
        #endregion

        #region public properties
        /// <summary>
        /// Error result
        /// </summary>
        public ErrorInfo ErrorMessage { set; get; }
        #endregion

        #region constructor
        public LocationComponent()
        {
            ErrorMessage = new ErrorInfo();
        }
        #endregion

        #region private methods
        /// <summary>
        /// clear error message
        /// </summary>
        private void ClearErrorMessage()
        {
            ErrorMessage.Status = string.Empty;
            ErrorMessage.ErrorCode = string.Empty;
            ErrorMessage.Message = string.Empty;
        }
        #endregion

        #region public mehods
        /// <summary>
        /// get Eics setup data to list
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public List<ObjectContext> Search(ObjectContext parameter)
        {
            StringBuilder sqlString = new StringBuilder();
            List<ObjectContext> items = new List<Entity.ObjectContext>();
            Location param = null;
            param = parameter == null ? null : (Location)parameter;
            using (Entities entities = new Entities())
            {
                entities.Open();
                sqlString.Append(SQL_QUERY);
                DbCommand cmd = entities.CreateCommand(sqlString.ToString());
                cmd.CommandType = System.Data.CommandType.Text;
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", param.AppId));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", param.Device));
                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", param.UserId));
                DbDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Location data = new Location();
                    data.LocationId = base.GetString(0, reader);
                    data.WhMasterId = base.GetString(1, reader);
                    data.PickAreaId = base.GetString(2, reader);
                    data.LocationName = base.GetString(3, reader);
                    data.Description = base.GetString(4, reader);
                    data.LocationType = base.GetString(5, reader);
                    data.Status = base.GetString(6, reader);
                    data.Length = (float?)base.GetDouble(7, reader);
                    data.Width = (float?)base.GetDouble(8, reader);
                    data.Height = (float?)base.GetDouble(9, reader);
                    data.Capacity = (float?)base.GetDouble(10, reader);

                    data.LpnController = base.GetString(11, reader);
                    data.MixOwners = base.GetString(12, reader);
                    data.MixItems = base.GetString(13, reader);
                    data.MixLot = base.GetString(14, reader);
                    data.MixExpireDate = base.GetString(15, reader);
                    data.PutAwaySequence = base.GetInt(16, reader);
                    data.PickSequence = base.GetInt(17, reader);
                    data.OverrideReceiveDate = base.GetString(18, reader);
                    data.IsActive = base.GetString(19, reader);
                    data.CreateDate = (DateTime)base.GetDate(20, reader);
                    data.CreateBy = base.GetString(21, reader);
                    data.NotChangeObject();
                    items.Add(data);
                }
                /*
                 "SELECT [location_id],[wh_master_id],[pick_area_id],[location],[description] " +
                            ",[loc_type],[location_status],[length],[width],[height],[capacity_qty],[lpn_controlled] " +
                            " ,[mix_owners],[mix_items],[mix_lot],[mix_expiry_dates],[putaway_sequence],[pick_sequence] " +
                            ",[override_receive_date],[is_active],[create_date],[create_by] " +
                 */
            }
            return items;
        }


        public override string GetInsertSQL(ObjectContext param)
        {
            Location parameter = (Location)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_INSERT);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId] ", BaseSQLModel.SqlWithQoute(parameter.UserId));
            sqlString.Replace("[@WhId]", BaseSQLModel.SqlWithQoute(parameter.WhMasterId));
            sqlString.Replace("[@PickAreaId]", BaseSQLModel.SqlWithQoute(parameter.PickAreaId));
            sqlString.Replace("[@LocationName]", BaseSQLModel.SqlWithQoute(parameter.LocationName));
            sqlString.Replace("[@Description]", BaseSQLModel.SqlWithQoute(parameter.Description));
            sqlString.Replace("[@LocationType]", BaseSQLModel.SqlWithQoute(parameter.LocationType));
            sqlString.Replace("[@Status]", BaseSQLModel.SqlWithQoute(parameter.Status));
            sqlString.Replace("[@Length]", BaseSQLModel.SqlWithoutQoute(parameter.Length));
            sqlString.Replace("[@Width]", BaseSQLModel.SqlWithoutQoute(parameter.Width));
            sqlString.Replace("[@Height]", BaseSQLModel.SqlWithoutQoute(parameter.Height));
            sqlString.Replace("[@Capacity]", BaseSQLModel.SqlWithoutQoute(parameter.Capacity));
            sqlString.Replace("[@LpnControl]", BaseSQLModel.SqlWithQoute(parameter.LpnController));
            sqlString.Replace("[@MixOwners]", BaseSQLModel.SqlWithQoute(parameter.MixOwners));
            sqlString.Replace("[@MixItems]", BaseSQLModel.SqlWithQoute(parameter.MixItems));
            sqlString.Replace("[@MixLot]", BaseSQLModel.SqlWithQoute(parameter.MixLot));
            sqlString.Replace("[@MixExpireDate]", BaseSQLModel.SqlWithQoute(parameter.MixExpireDate));
            sqlString.Replace("[@PutAway]", BaseSQLModel.SqlWithoutQoute(parameter.PutAwaySequence));
            sqlString.Replace("[@PickSequence]", BaseSQLModel.SqlWithoutQoute(parameter.PickSequence));
            sqlString.Replace("[@OverrideReceiveDate]", BaseSQLModel.SqlWithQoute(parameter.OverrideReceiveDate));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            return sqlString.ToString();
            /*
             "@in_vchApplicationID = [@AppId], @in_vchDevice = [@Device], @in_vchUserID = [@UserId] " +
                                        ",@in_vchWhMasterID  = [@WhId] " +
                                        ",@in_vchLocationID = [@LocationId] " +
                                        ",@in_vchPickAreaID = [@PickAreaId] " +
                                        ",@in_vchLocation		 = [@LocationName] " +
                                        ",@in_vchDescription = [@Description] " +
                                        ",@in_vchLoc_type	 = [@LocationType] " +
                                        ",@in_vchLocationStatus = [@Status] " +
                                        ",@in_intLength = [@Length] " +
                                        ",@in_intWidth = [@Width] " +
                                        ",@in_intHeight = [@Height] " +
                                        ",@in_intCapacityQty	 = [@Capacity] " +
                                        ",@in_vchLpnControlled= [@LpnControl] " +
                                        ",@in_vchMixOwners = [@MixOwners] " +
                                        ",@in_vchMixItems	= [MixItems] " +
                                        ",@in_vchMixLot	 = [@MixLot] " +
                                        ",@in_vchMixExpiryDates	 = [@MixExpireDate] " +
                                        ",@in_intPutawaySequence = [@PutAway] " +
                                        ",@in_intPickSequence		= [@PickSequence] " +
                                        ",@in_vchOverrideReceiveDate = [@OverrideReceiveDate] " +
                                        ",@in_vchIsActive	 = [@IsActive]";
             */
        }

        public override string GetUpdateSQL(ObjectContext param)
        {
            Location parameter = (Location)param;
            StringBuilder sqlString = new StringBuilder();
            sqlString.Append(SQL_PROC_UPDATE);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            sqlString.Replace("[@WhId]", BaseSQLModel.SqlWithQoute(parameter.WhMasterId));
            sqlString.Replace("[@LocationId]", BaseSQLModel.SqlWithQoute(parameter.LocationId));
            sqlString.Replace("[@PickAreaId]", BaseSQLModel.SqlWithQoute(parameter.PickAreaId));
            sqlString.Replace("[@LocationName]", BaseSQLModel.SqlWithQoute(parameter.LocationName));
            sqlString.Replace("[@Description]", BaseSQLModel.SqlWithQoute(parameter.Description));
            sqlString.Replace("[@LocationType]", BaseSQLModel.SqlWithQoute(parameter.LocationType));
            sqlString.Replace("[@Status]", BaseSQLModel.SqlWithQoute(parameter.Status));
            sqlString.Replace("[@Length]", parameter.Length == null? "NULL": BaseSQLModel.SqlWithoutQoute(parameter.Length));
            sqlString.Replace("[@Width]", parameter.Width == null ? "NULL" : BaseSQLModel.SqlWithoutQoute(parameter.Width));
            sqlString.Replace("[@Height]", parameter.Height == null ? "NULL" : BaseSQLModel.SqlWithoutQoute(parameter.Height));
            sqlString.Replace("[@Capacity]", parameter.Capacity == null ? "NULL" : BaseSQLModel.SqlWithoutQoute(parameter.Capacity));
            sqlString.Replace("[@LpnControl]", BaseSQLModel.SqlWithQoute(parameter.LpnController));
            sqlString.Replace("[@MixOwners]", BaseSQLModel.SqlWithQoute(parameter.MixOwners));
            sqlString.Replace("[@MixItems]", BaseSQLModel.SqlWithQoute(parameter.MixItems));
            sqlString.Replace("[@MixLot]", BaseSQLModel.SqlWithQoute(parameter.MixLot));
            sqlString.Replace("[@MixExpireDate]", BaseSQLModel.SqlWithQoute(parameter.MixExpireDate));
            sqlString.Replace("[@PutAway]", parameter.PutAwaySequence == null ? "NULL" : BaseSQLModel.SqlWithoutQoute(parameter.PutAwaySequence));
            sqlString.Replace("[@PickSequence]", parameter.PickSequence == null ? "NULL" : BaseSQLModel.SqlWithoutQoute(parameter.PickSequence));
            sqlString.Replace("[@OverrideReceiveDate]", BaseSQLModel.SqlWithQoute(parameter.OverrideReceiveDate));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));

            return sqlString.ToString();
        }

        public override string GetDeleteSQL(ObjectContext param)
        {
            Location parameter = (Location)param;
            StringBuilder sqlString = new StringBuilder();
            //insert command
            sqlString.Append(SQL_PROC_DELETE);
            sqlString.Replace("[@AppId]", BaseSQLModel.SqlWithQoute(parameter.AppId));
            sqlString.Replace("[@Device]", BaseSQLModel.SqlWithQoute(parameter.Device));
            sqlString.Replace("[@UserId]", BaseSQLModel.SqlWithQoute(parameter.UserId));
            sqlString.Replace("[@LocationId]", BaseSQLModel.SqlWithQoute(parameter.LocationId));
            sqlString.Replace("[@IsActive]", BaseSQLModel.SqlWithQoute(parameter.IsActive));
            return sqlString.ToString();
        }
        /// <summary>
        /// Insert to data source
        /// </summary>
        /// <param name="param"></param>
        public override void Insert(ObjectContext param)
        {
          
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetInsertSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("Zone insert unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// update to datasource
        /// </summary>
        /// <param name="param"></param>
        public override void Update(ObjectContext param)
        {
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetUpdateSQL(param));
                    entities.SetCommand(cmd);
                    int m_iRecordChanged = entities.SaveChanges();
                    if (m_iRecordChanged < 1)
                    {
                        throw new Exception("zone update unsuccess.");
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// delete from datasource
        /// </summary>
        public override void Delete(ObjectContext param)
        {
            DataSet ds = new DataSet();
            ClearErrorMessage();
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DbCommand cmd = entities.CreateCommand(GetDeleteSQL(param));
                    DbDataAdapter adapter = entities.CreateDataAdapter(cmd);
                    adapter.Fill(ds, dataTableName);
                    if (ds.Tables[dataTableName].Rows.Count > 0)
                    {
                        if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                        {
                            ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                            ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                            ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                            throw new Exception(ErrorMessage.Message);
                        }
                    }
                }
                transaction.Complete();
            }
        }
        /// <summary>
        /// special case for store procedure to update data that return result as record
        /// </summary>
        /// <param name="items"></param>
        public override void SaveChanges(List<ObjectContext> items)
        {
            string whMasterId = string.Empty;
            Location parameter = null;
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    DataSet ds = new DataSet();
                    DbCommand cmd = entities.CreateCommand(SQL_GET_WH_MASTER_ID);
                    DbDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        whMasterId = GetString(0, reader);
                    }
                    reader.Close();
                    DbDataAdapter adapter = entities.CreateDataAdapter(sqlCmd);
                    adapter.SelectCommand = cmd;
                    cmd.CommandType = CommandType.StoredProcedure;
                    foreach (ObjectContext item in items)
                    {
                        parameter = (Location)item;
                        
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                //master id
                                parameter.WhMasterId = whMasterId;

                                sqlCmd = GetInsertSQL(item);
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                //cmd.Parameters.Clear();
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchApplicationID", parameter.AppId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchWhMasterID", parameter.WhMasterId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchPickAreaID", parameter.PickAreaId));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchLocation", parameter.LocationName));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDescription", parameter.Description));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchLoc_type", parameter.LocationType));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchLocationStatus", parameter.Status));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intLength", parameter.Length));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intWidth", parameter.Width));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intHeight", parameter.Height));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intCapacityQty", parameter.Capacity));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchLpnControlled", parameter.LpnController));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMixOwners", parameter.MixOwners));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMixItems", parameter.MixItems));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMixLot", parameter.MixLot));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchMixExpiryDates", parameter.MixExpireDate));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intPutawaySequence", parameter.PutAwaySequence));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_intPickSequence", parameter.PickSequence));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchOverrideReceiveDate", parameter.OverrideReceiveDate));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchIsActive", parameter.IsActive));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchDevice", parameter.Device));
                                //cmd.Parameters.Add(entities.CreateParameter("@in_vchUserID", parameter.UserId));
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                cmd.CommandType = CommandType.Text;
                                cmd.CommandText = sqlCmd;
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            adapter.SelectCommand = cmd;
                            adapter.Fill(ds, dataTableName);
                            if (ds.Tables[dataTableName].Rows.Count > 0)
                            {
                                if (!ds.Tables[dataTableName].Rows[0]["status"].ToString().Equals("PASS"))
                                {
                                    ErrorMessage.Status = ds.Tables[dataTableName].Rows[0]["Status"].ToString();
                                    ErrorMessage.ErrorCode = ds.Tables[dataTableName].Rows[0]["ErrorCode"].ToString();
                                    ErrorMessage.Message = ds.Tables[dataTableName].Rows[0]["Message"].ToString();
                                    throw new Exception(ErrorMessage.Message);
                                }
                            }
                            //item.NotChangeObject();
                        }
                    }
                   
                }
                transaction.Complete();
                items.RemoveAll(o => o.ObjectStatus == OBJECT_STATE.Delete);
                foreach (ObjectContext item in items)
                {
                    item.NotChangeObject();
                }
            }
        }
        #endregion
    }
}
